package com.kcsj.dao;

import com.kcsj.domain.Address;
import com.kcsj.domain.user.Customer;
import com.kcsj.service.user.CustomerService;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;

public class AddressDao {
    /**
     * @author 傅立贤
     * 29th Nov. 2020
     */
    private static final AddressDao addressDao = new AddressDao();
    private AddressDao() {
    }

    public static AddressDao getInstance() {
        return addressDao;
    }

    public boolean add(Address address, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String add_sql = "INSERT INTO address(customer_id,address,description)" +
                " value (?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(add_sql);
        preparedStatement.setInt(1,address.getCustomer().getId());
        preparedStatement.setString(2,address.getAddress());
        preparedStatement.setString(3,address.getDescription());
        //如果执行影响行数大于0，则增加成功
        int rowsAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return rowsAffected>0;
    }

    public Collection<Address> findAllByCustomerId(Integer customerId, Connection conn)
            throws SQLException {
        ArrayList<Address> addresses = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder sql = new StringBuilder("select * from address where customer_id=?");
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        preparedStatement.setInt(1,customerId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String address = resultSet.getString("address");
            Integer id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            Customer customer = CustomerService.getInstance().findInfo(customerId);
            Address customer_address = new Address(id,customer, address, description);
            addresses.add(customer_address);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return addresses;
    }

    public boolean update(Address address, Connection conn) throws SQLException {
        String update_sql = "UPDATE address SET address =?,description=? WHERE id = ?";
        PreparedStatement pstmt = conn.prepareStatement(update_sql);
        pstmt.setString(1,address.getAddress());
        pstmt.setString(2,address.getDescription());
        pstmt.setInt(3,address.getId());
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }

    public boolean delete(Integer id,Connection conn) throws SQLException {
        String sql = "delete from address where id=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        int rowsAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return rowsAffected>0;
    }
}
